
/*
 * @(#)MySqlODPStorer.java	0.1 01/06/05
 *
 * Copyright 2006 Fabio Gasparetti. All rights reserved.
 */

package org.tabularium.text.se.odp;

import java.text.CharacterIterator;
import java.text.StringCharacterIterator;
import java.util.*;
import java.sql.*;
import java.util.logging.*;
import java.io.*;

/**
 * A ODPStorer instantiated on MySQL.
 */
public class MySqlODPStorer implements ODPStorer {

	protected Connection connection;

	protected String dbname = null;

	protected Logger logger = Logger.getLogger("org.tabularium.log.http.ODP");

	protected String readLock = "LOCK TABLES odp READ;";

	protected String writeLock = "LOCK TABLES odp WRITE;";

	protected String unlock = "UNLOCK TABLES;";

	protected String odp = "CREATE TABLE odp "
			+ "(id INT NOT NULL AUTO_INCREMENT, " + "topic BLOB, "
			+ "catid INT, " + "title BLOB, " + "link BLOB, "
			+ "pagetitle BLOB, " + "pagedescription BLOB, "
			+ "PRIMARY KEY id (id), " + "KEY topic_idx (topic(255)), "
			+ "KEY link_idx (link(50)));";

	protected String resetOdp = "DELETE FROM odp;";

	protected String getLinksByTopicRecur = "SELECT * FROM odp WHERE topic REGEXP ?;";

	protected String getLinksByTopic = "SELECT * FROM odp " + "WHERE topic=?;";

	protected String getChilds = "SELECT DISTINCT topic FROM odp WHERE topic REGEXP ? ORDER BY topic;";

	protected String getLinkByUrl = "SELECT * FROM odp " + "WHERE link=?;";

	protected String getLinkById = "SELECT * FROM odp " + "WHERE id=?;";

	protected String addLink = "INSERT INTO odp (topic, catid, title, link, pagetitle, pagedescription) "
			+ "VALUES (?, ?, ?, ?, ?, ?);";

	protected String getAllLinks = "SELECT id FROM odp;";

	protected String getAllTopics = "SELECT DISTINCT topic FROM odp;";

	protected String getDescendentTopics = "SELECT DISTINCT topic FROM odp WHERE topic REGEXP ?;";

	protected String numLinks = "SELECT COUNT(*) FROM odp;";

	/**
	 * JdbcDriver=com.mysql.jdbc.Driver JdbcLogin=root JdbcPassword=
	 * JdbcUrl=jdbc:mysql://127.0.0.1/crawler_data
	 */
	public MySqlODPStorer() {
	}

	public void init(String driver, String url, String creationUrl,
			String dbname, boolean reset) throws ClassNotFoundException,
			SQLException {
		PreparedStatement stat;
		this.dbname = dbname;
		if ((driver == null) || (url == null)) {
			throw new IllegalArgumentException();
		}

		try {
			Class.forName(driver).newInstance();
		} catch (Exception ex) {
			// IllegalAccessException and InstantiationException exceptions
			throw new ClassNotFoundException("Unable to instantiate " + driver
					+ " class");
		}

		try {
			connection = DriverManager.getConnection(url);
		} catch (SQLException ex) {
			if (ex.getErrorCode() == 1049) { // unknown database
				reset = true;
			} else {
				throw ex;
			}
		}

		if (reset) {
			createDb(creationUrl);
		}
		stat = connection.prepareStatement("USE " + dbname + ";");
		stat.execute();
		stat.close();
	}

	/**
	 * 
	 * @throws IOException
	 */
	public void clear() throws ODPStorerException {
		PreparedStatement stat;
		try {
			stat = connection.prepareStatement(writeLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(resetOdp);
			stat.executeUpdate();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
	}

	/**
	 * 
	 * @param e
	 *            ODPEntry
	 * @throws IOException
	 */
	public void add(ODPEntry e) throws ODPStorerException {
		PreparedStatement stat;
		try {
			stat = connection.prepareStatement(writeLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(addLink);
			stat.setString(1, e.topic);
			stat.setString(2, e.catid);
			stat.setString(3, e.title);
			stat.setString(4, e.link);
			stat.setString(5, e.pagetitle);
			stat.setString(6, e.pagedescription);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
	}

	/**
	 * 
	 * @param entry
	 *            ODPEntry
	 * @throws IOException
	 * @return ODPEntry
	 */
	public ODPEntry get(ODPEntry entry) throws ODPStorerException {
		ODPEntry e = null;
		ResultSet rs;
		PreparedStatement stat;
		try {
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getLinkById);
			stat.setString(1, entry.id);
			rs = stat.executeQuery();
			if (rs.next()) {
				e = new ODPEntry();
				e.id = rs.getString("id");
				e.topic = rs.getString("topic");
				e.catid = rs.getString("catid");
				e.title = rs.getString("title");
				e.link = rs.getString("link");
				e.pagetitle = rs.getString("pagetitle");
				e.pagedescription = rs.getString("pagedescription");
			}
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		return e;
	}

	/**
	 * 
	 * @param url
	 *            String
	 * @throws IOException
	 * @return ODPEntry
	 */
	public ODPEntry get(String url) throws ODPStorerException {
		ODPEntry e = null;
		ResultSet rs;
		PreparedStatement stat;
		try {
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getLinkByUrl);
			stat.setString(1, url);
			rs = stat.executeQuery();
			if (rs.next()) {
				e = new ODPEntry();
				e.id = rs.getString("id");
				e.topic = rs.getString("topic");
				e.catid = rs.getString("catid");
				e.title = rs.getString("title");
				e.link = rs.getString("link");
				e.pagetitle = rs.getString("pagetitle");
				e.pagedescription = rs.getString("pagedescription");
			}
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		return e;
	}

	public void remove(ODPEntry e) throws ODPStorerException {
		/** @todo Method to be implemented */
		/*
		 * PreparedStatement stat; try { stat =
		 * connection.prepareStatement(writeLock); stat.execute(); // remove
		 * entry references from the sessions table int entryId = getEntryId(e);
		 * if (entryId == -1) throw new IOException("Entry not found"); stat =
		 * connection.prepareStatement(removeEntryFromSessions); stat.setInt(1,
		 * entryId); stat.executeUpdate(); stat =
		 * connection.prepareStatement(removeEntry); stat.setString(1,
		 * e.timestamp); stat.setString(2, e.clientAddress);
		 * stat.executeUpdate(); stat = connection.prepareStatement(unlock);
		 * stat.execute(); } catch (Exception ex) { throw new IOException("MySql
		 * exception: "+ex.toString()); }
		 */
	}

	public void remove(String link) throws ODPStorerException {
		/*
		 * PreparedStatement stat; ResultSet rs; try { stat =
		 * connection.prepareStatement(writeLock); stat.execute(); // remove
		 * clientAddress's entry references in the sessions table stat =
		 * connection.prepareStatement(getEntries); stat.setString(1,
		 * clientAddress); rs = stat.executeQuery(); while (rs.next()) { int
		 * entryId = rs.getInt("id"); stat =
		 * connection.prepareStatement(removeEntryFromSessions); stat.setInt(1,
		 * entryId); stat.executeUpdate(); } stat =
		 * connection.prepareStatement(removeEntries); stat.setString(1,
		 * clientAddress); stat.executeUpdate(); stat =
		 * connection.prepareStatement(unlock); stat.execute(); } catch
		 * (Exception ex) { throw new IOException("MySql exception:
		 * "+ex.toString()); }
		 */
	}

	public ODPEntryIterator browse() throws ODPStorerException {
		ArrayList ids = new ArrayList();
		ResultSet rs;
		PreparedStatement stat;
		try {
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getAllLinks);
			rs = stat.executeQuery();
			while (rs.next()) {
				ids.add(rs.getString("id"));
			}
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		int n2[] = new int[ids.size()];
		Iterator iter = ids.iterator();
		for (int i = 0; iter.hasNext(); i++) {
			String s = (String) iter.next();
			n2[i] = Integer.parseInt(s);
		}
		return new MySqlODPEntryIterator(this, n2);
	}

	public String[] topics() throws ODPStorerException {
		ArrayList topics = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getAllTopics);
			rs = stat.executeQuery();
			while (rs.next()) {
				topics.add(rs.getString("topic"));
			}
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		return (String[]) topics.toArray(new String[] {});
	}
	
	public String[] topics(String root) throws ODPStorerException {
		ArrayList topics = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getDescendentTopics);
			stat.setString(1, "^"+MySqlODPStorer.forRegex(root)+"/[^/]+.*$");
			rs = stat.executeQuery();
			while (rs.next()) {
				topics.add(rs.getString("topic"));
			}
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		return (String[]) topics.toArray(new String[] {});
	}
	
	public long size() throws ODPStorerException {
		ResultSet rs;
		PreparedStatement stat;
		long n = 0l;
		try {
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(numLinks);
			rs = stat.executeQuery();
			if (rs.next())
				n = rs.getInt(1);
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		return n;
	}

	public ODPEntryIterator browse(String topic) throws ODPStorerException {
		ArrayList ids = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getLinksByTopicRecur);
			stat.setString(1, "^"+MySqlODPStorer.forRegex(topic)+".*$");
			rs = stat.executeQuery();
			while (rs.next()) {
				ids.add(rs.getString("id"));
			}
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		int n2[] = new int[ids.size()];
		Iterator iter = ids.iterator();
		for (int i = 0; iter.hasNext(); i++) {
			String s = (String) iter.next();
			n2[i] = Integer.parseInt(s);
		}
		return new MySqlODPEntryIterator(this, n2);
	}

	public String[] links(String topic) throws ODPStorerException {
		ArrayList links = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getLinksByTopic);
			stat.setString(1, topic);
			rs = stat.executeQuery();
			while (rs.next()) {
				links.add(rs.getString("link"));
			}
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		return (String[])links.toArray(new String[] {});
	}
	
	public ODPEntry[] entries(String topic) throws ODPStorerException {
		ArrayList entries = new ArrayList();
		try {
			ODPEntry e;
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getLinksByTopic);
			stat.setString(1, topic);
			rs = stat.executeQuery();
			while (rs.next()) {
				e = new ODPEntry();
				e.id = rs.getString("id");
				e.topic = rs.getString("topic");
				e.catid = rs.getString("catid");
				e.title = rs.getString("title");
				e.link = rs.getString("link");
				e.pagetitle = rs.getString("pagetitle");
				e.pagedescription = rs.getString("pagedescription");
				entries.add(e);
			}
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		return (ODPEntry[])entries.toArray(new ODPEntry[] {});
	}

	
	protected void createDb(String url) throws SQLException {
		connection = DriverManager.getConnection(url);

		PreparedStatement stat;
		stat = connection.prepareStatement("DROP DATABASE IF EXISTS " + dbname
				+ ";");
		stat.execute();
		stat.close();

		stat = connection.prepareStatement("CREATE DATABASE " + dbname + ";");
		stat.execute();
		stat.close();
		stat = connection.prepareStatement("USE " + dbname + ";");
		stat.execute();
		stat.close();
		stat = connection.prepareStatement(odp);
		stat.execute();
		stat.close();
	}

	public String[] childs(String topic) throws ODPStorerException {
		ArrayList topics = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getChilds);
			//stat.setString(1, "^"+topic);
			stat.setString(1, "^"+MySqlODPStorer.forRegex(topic)+"/[^/]+$");
			rs = stat.executeQuery();
			while (rs.next()) {
				try {
					topics.add(rs.getString("topic"));
				} catch (Exception ex) {
					logger.severe("Exception in getString(topic): "+ ex.toString());
				}
			}
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			//System.out.println("^"+MySqlODPStorer.forRegex(topic)+"/[^/]+$");
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		return (String[]) topics.toArray(new String[] {});
	}
	
	
	public String[] leafs(String topic) throws ODPStorerException {
		ArrayList topics = new ArrayList();
		String prev = null, s;
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getChilds);
			//stat.setString(1, "^"+topic);
			stat.setString(1, "^"+MySqlODPStorer.forRegex(topic)+"/.+$");
			rs = stat.executeQuery();
			while (rs.next()) {
				s = rs.getString("topic");
				if ((prev == null) || ODP.included(prev, s)) {
					prev = s;
					continue;
				}
				topics.add(prev);
				prev = s;
			}
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			//System.out.println("^"+MySqlODPStorer.forRegex(topic)+"/[^/]+$");
			throw new ODPStorerException("MySql exception: " + ex.toString());
		}
		return (String[]) topics.toArray(new String[] {});
	}
	
	
	public static String forRegex(String aRegexFragment){
		    final StringBuilder result = new StringBuilder();

		    final StringCharacterIterator iterator = 
		      new StringCharacterIterator(aRegexFragment)
		    ;
		    char character =  iterator.current();
		    while (character != CharacterIterator.DONE ){
		      /*
		      * All literals need to have backslashes doubled.
		      */
		      if (character == '.') {
		        result.append("\\.");
		      }
		      else if (character == '\\') {
		        result.append("\\\\");
		      }
		      else if (character == '?') {
		        result.append("\\?");
		      }
		      else if (character == '*') {
		        result.append("\\*");
		      }
		      else if (character == '+') {
		        result.append("\\+");
		      }
		      else if (character == '&') {
		        result.append("\\&");
		      }
		      else if (character == ':') {
		        result.append("\\:");
		      }
		      else if (character == '{') {
		        result.append("\\{");
		      }
		      else if (character == '}') {
		        result.append("\\}");
		      }
		      else if (character == '[') {
		        result.append("\\[");
		      }
		      else if (character == ']') {
		        result.append("\\]");
		      }
		      else if (character == '(') {
		        result.append("\\(");
		      }
		      else if (character == ')') {
		        result.append("\\)");
		      }
		      else if (character == '^') {
		        result.append("\\^");
		      }
		      else if (character == '$') {
		        result.append("\\$");
		      }
		      else {
		        //the char is not a special one
		        //add it to the result as is
		        result.append(character);
		      }
		      character = iterator.next();
		    }
		    return result.toString();
		  }

	
}
